"""
编程实现：循环输入10个人的信息（姓名，年龄，性别，身高），并存储到数据库中
create   table  person(
	username  varchar(20),
	age   int,
	sex 	  char(4),
	high  int
)
"""
import  pymysql

# info = (("张三",12,"男",132),
#         ("李四",13,"女",198),
#         ("王五",14,"男",176),
#         ("李梅",23,"女",156),
#         ("东方欧柏",24,"男",188),
#         ("李不会",32,"女",174),
#         ("张梅",33,"女",177),
#         ("苏白",18,"男",171),
#         ("沈欣妍",24,"女",164),
#         ("吴磊",28,"男",154))
#
#创建数据库链接
# db = pymysql.connect(host='localhost',user="root",password="root",database="Python_Database",port=3306,charset="utf8")
#通过链接创建游标
# cursor = db.cursor()
# for i in info:
#循环执行数据库代码
#     sql = f"insert into user values{i}"
#     cursor.execute(sql)
#对改变数据库的操作要进行提交否则相当于没有操作
#     db.commit()
#关闭游标
# cursor.close()
#关闭数据库
# db.close()


"""
编程实现：使用openpyxl在excel中写入10列2000行的数据，然后使用openpyxl读取出来并使用pymysql写入到数据库中。
CREATE TABLE `user_info` (
  `id` int(11) ,
  `idcard` varchar(50) ,
  `username` varchar(50) ,
  `realname` varchar(50) ,
  `pwd` varchar(50) ,
  `telphone` varchar(12) ,
  `email` varchar(100) ,
  `age` int(11) ,
  `sex` varchar(20) ,
  `address` varchar(200) ,
  `hiredate` date ,
  `sal` double(9,2) ,
  `job` varchar(100) ,
  `company` varchar(100)
) ;
(实现之后思考如何提高写入速度：可以考虑executemany方法，嘿嘿！！)
"""
import openpyxl
#打开文件
# workbook = openpyxl.Workbook("user.xlsx")
#创建工作表
# workbook.create_sheet(title="sheet1")
#保存做出的改变
# workbook.save('user.xlsx')

"""
插入2000行10列数据
"""
# workbook = openpyxl.load_workbook('user.xlsx')
# worksheet = workbook['sheet1']
# for i in range(1,2001):
#     for j in range(1,11):
#         worksheet.cell(i,j).value = i+j
# workbook.save('user.xlsx')

"""
读取信息
"""
# workbook = openpyxl.load_workbook('user.xlsx')
# worksheet = workbook['sheet1']
# tup0 = []
# for i in worksheet.iter_rows(1,2000):
#     tup = ()
#     for j in i:
#         tup=tup+(j.value,)
#     tup0.append(tup)
# tup0 = tuple(tup0)
# print(len(tup0))
"""
插入数据库中
"""
# db = pymysql.connect(host='localhost',user="root",password="root",database="Python_Database",port=3306,charset="utf8")
# cursor = db.cursor()
# for i in tup0:
#     sql = f"insert into intshu values{i}"
#     cursor.execute(sql)
#     db.commit()
#     print(i[1], type(i))
# cursor.close()
# db.close()

"""
查询练习，与任务无关
"""
# file = open('read.txt','a',encoding='utf-8')
# file.write("hello python ")
# # r=file.write()
# # print(r)
# file.close()

# db = pymysql.connect(host='localhost',user="root",password="root",database="Python_Database",port=3306,charset="utf8")
# cursor = db.cursor()
# sql = "select * from user"
# cursor.execute(sql)
# data = cursor.fetchmany(7)
# print(data)
# cursor.close()
# db.close()

# !/usr/bin/python
# -*- coding: UTF-8 -*-

